package cn.edu.sdjzu.xg.eagle.dao.mapper;//package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.eagle.dao.UserAssRoleDao;
import cn.edu.sdjzu.xg.eagle.entity.User;
import cn.edu.sdjzu.xg.eagle.entity.authority.Role;
import cn.edu.sdjzu.xg.eagle.entity.authority.UserAssRole;
import cn.edu.sdjzu.xg.eagle.factory.ServiceFactory;
import cn.edu.sdjzu.xg.eagle.service.RoleService;
import cn.edu.sdjzu.xg.eagle.service.UserService;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public final class UserAssRoleDaoImpl implements UserAssRoleDao {
    private static UserService userService = null;
    private static RoleService roleService = null;
    //getService可能抛出异常，故不能简单地在声明静态变量时直接赋值。而静态变量可以用static块来捕捉异常
    static {
        try{
            userService = ServiceFactory.getService(UserService.class, "UserServiceImpl");
            roleService = ServiceFactory.getService(RoleService.class, "RoleServiceImpl");
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public boolean add(Connection connection, UserAssRole userAssRole) throws SQLException {
        //准备预编译的语句
        String sql_insert = "INSERT INTO bysj.userAssRole " +
                "( user_id, role_id) VALUES (?, ?)";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_insert);

        pstmt_update.setInt(1, userAssRole.getUser().getId());
        pstmt_update.setInt(2, userAssRole.getRole().getId());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update);
        return rowAffected > 0;
    }

    @Override
    public UserAssRole find(Connection connection, Integer id) throws SQLException, InvocationTargetException {
        //创建UserAssRole类型的引用，暂不创建对象
        UserAssRole desiredUserAssRole = null;

        String sql_select_id = "SELECT * FROM userAssRole WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()) {
            int user_id = resultSet_select_id.getInt("user_id");
            int roleId = resultSet_select_id.getInt("role_id");
            //获得当前UserAssRole关联的User
            User user = userService.find(user_id);
            //获得当前UserAssRole关联的Role
            Role role = roleService.find(roleId);
            desiredUserAssRole = new UserAssRole(id, user, role);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredUserAssRole;
    }

    /**
     * 查找该User对象所拥有的UserAssRole对象
     * 用于权限管理中的角色-菜单展示
     *
     * @param connection
     * @param user
     * @return
     * @throws SQLException
     */
    @Override
    public Collection<UserAssRole> findAll(Connection connection, User user) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的UserAssRole对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<UserAssRole> desiredUserAssRoles = new TreeSet<UserAssRole>();

        //准备预编译的语句
        String sql_select_all = "SELECT * FROM User_Ass_Role WHERE user_id = ?";
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select_all);
        pstmt_select_all.setInt(1, user.getId());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            //获得userAssRole表中当前记录的id字段的值
            int user_id = resultSet_select_all.getInt("user_id");
            int roleId = resultSet_select_all.getInt("role_id");
            //获得当前UserAssRole关联的Role
            Role role = roleService.find(roleId);
            UserAssRole desiredUserAssRole =
                    new UserAssRole(id, user, role);
            //将当前记录代表的UserAssRole对象添加到desiredUserAssRoles中
            desiredUserAssRoles.add(desiredUserAssRole);
        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all);
        return desiredUserAssRoles;
    }

    @Override
    public Collection<UserAssRole> findAll(Connection connection) throws SQLException, InvocationTargetException {
        //创建集合类对象，用来保存所有记录代表的UserAssRole对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<UserAssRole> desiredUserAssRoles = new TreeSet<UserAssRole>();
        //准备预编译的语句
        String sql_select_all = "SELECT * FROM UserAssRole";
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select_all);
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            //获得userAssRole表中当前记录的id字段的值
            int user_id = resultSet_select_all.getInt("user_id");
            int roleId = resultSet_select_all.getInt("role_id");
            //获得当前UserAssRole关联的User
            User user = userService.find(user_id);
            //获得当前UserAssRole关联的Role
            Role role = roleService.find(roleId);
            UserAssRole desiredUserAssRole =
                    new UserAssRole(id, user, role);
            //将当前记录代表的UserAssRole对象添加到desiredUserAssRoles中
            desiredUserAssRoles.add(desiredUserAssRole);
        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all);
        return desiredUserAssRoles;
    }

    @Override
    public boolean update(Connection connection, UserAssRole userAssRole) throws SQLException {
        //准备预编译的语句
        String sql_update =
                "UPDATE userAssRole SET user_id = ?, role_id = ? " +
                        "WHERE id = ?";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_update);

        pstmt_update.setInt(1, userAssRole.getUser().getId());
        pstmt_update.setInt(2, userAssRole.getRole().getId());
        pstmt_update.setInt(3, userAssRole.getId());


        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update);
        return rowAffected > 0;
    }

    @Override
    public boolean delete(Connection connection, Integer id) throws SQLException {
        //准备预编译的语句
        String sql_delete_id = "DELETE FROM userAssRole WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);
        pstmt_delete.setInt(1, id);
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_delete.executeUpdate();

        JdbcHelper.close(pstmt_delete);
        return rowAffected > 0;
    }
}

